﻿CREATE PROCEDURE [maint].[GetDeviceBackupSet]
@DbName [sysname], @DeviceName [sysname], @BackupSetName [sysname] OUTPUT, @BackupSetExpireDate DATETIME OUTPUT, @OutputDiagnostics BIT=0
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @RC int, @ERROR int;
	SELECT @RC = 0, @ERROR = 0;

	IF(@OutputDiagnostics = 1)
		BEGIN
			PRINT OBJECT_NAME(@@PROCID)
			PRINT N'	@DbName = N''' + ISNULL(@DbName,N'NULL') + ''''
			PRINT N'	@DeviceName = N''' + ISNULL(@DeviceName,N'NULL') + ''''
		END

	BEGIN TRY
		EXEC @RC = util.CheckDevice @DeviceName, @OutputDiagnostics;
	END TRY
	BEGIN CATCH
		GOTO EXIT_PROC
	END CATCH

	DECLARE @OutputTableName nvarchar(36)
	EXEC @RC = maint.GetDeviceHeader @DbName, @DeviceName, @OutputTableName OUTPUT, @OutputDiagnostics;
	IF(@RC <> 0)
		GOTO EXIT_PROC

	--get backup name and expiration date
	DECLARE @QuerySQL nvarchar(512)
	SET @QuerySQL = N'
	SELECT
		@BackupSetName = [BackupName]
		,@BackupSetExpireDate = CONVERT(datetime,[ExpirationDate],120)
	FROM '
	+ @OutputTableName
	+ ' WHERE  ([BackupType] = 1)
		AND([Position] = 1)'

	IF(@OutputDiagnostics = 1)
		PRINT N'	@QuerySQL = ''' + ISNULL(@QuerySQL,N'NULL') + ''''

	EXEC @RC = sp_executesql @QuerySQL
		,N'@BackupSetName nvarchar(128) OUTPUT ,@BackupSetExpireDate datetime OUTPUT'
		,@BackupSetName = @BackupSetName OUTPUT
		,@BackupSetExpireDate = @BackupSetExpireDate OUTPUT

	IF(@BackupSetName IS NULL)
	OR(@RC <> 0)
		RAISERROR('Device %s does not contain a valid DBA database backup',16,1,@DeviceName);

EXIT_PROC:
	RETURN(@RC)	
END